package datenhaltung;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.concurrent.ExecutionException;

import util.Car;
import util.EngineType;
import util.Colour;


public class Datenhaltung extends DB_Basis implements IDatenhaltung{
	
	public Datenhaltung() {
		super("car");
		
	}
	
    public void saveCar( Car theCar ) throws ExecutionException {
    	
    	//update or insert        	
    	int updateOrInsert;
		try {
			updateOrInsert = this.countByCondition("ident_nr = '"+theCar.getIdentificationNumber()+"'");
		   	this.dbClose();
	    	
	    	if(updateOrInsert == 0){    		
	    		String insertValues = this.convertObjektToInsertString(theCar);  
	    		
	    		this.insert(insertValues);
	    		this.dbClose();
	  
	    	}else{    		
	    		String changes = this.buildFieldEqualsValue(theCar, " , ");  
	    		
	    		changes = changes.substring( 2 );		
	    		
	    		this.update(changes, "ident_nr = '"+theCar.getIdentificationNumber()+"'");
	    		this.dbClose();
	    	}    
		} catch (ClassNotFoundException e) {
			throw new ExecutionException(e);
		} catch (SQLException e) {
			throw new ExecutionException(e);
		}
 	    	
    	//column ident_nr,manufacturer,type,model,colour,engine_type,purchase_price,price,selled,year,milage    	    	
	}    
    
    public void deleteCar( String identificationNumberCar ) throws ExecutionException {
    	
    	try {
			this.delete("ident_nr = '"+identificationNumberCar + "'");
			this.dbClose();
		} catch (ClassNotFoundException e) { 
			throw new ExecutionException(e);
		} catch (SQLException e) {
			throw new ExecutionException(e);
		}
	}
      
    public Car[] getCars( Car searchConditions ) {
    	
    	String condition = " 1=1 ";
    	condition += this.buildFieldEqualsValue(searchConditions, " AND ");
    	ArrayList<Car> ca = new ArrayList<Car>();
	     	
    	ResultSet rs;
		try {
			rs = this.select(condition);
			while( rs.next() ) {
				ca.add( this.buildCar(rs) );
			}
			
		} catch (SQLException e) {}
		catch (ClassNotFoundException e) {}  
    	   
		Car[] ret = new Car[ca.size()];

    	for( int i = 0; i<ca.size(); i++ )
    	ret[i] = ca.get(i);

    	return ret;
    	
	}
    
    public int getCountAllCars() {
    	
    	int count = 0;
		try {
			count = this.countByCondition("1=1");
			this.dbClose();
		} catch (ClassNotFoundException e) {}
		catch (SQLException e) {}
    	
    	return count;
	}
    
	private String buildFieldEqualsValue(Car searchConditions,String connector){
    	    	
		String retString = "";
		
    	if ( searchConditions.getIdentificationNumber() != null ) 
    		if(searchConditions.getIdentificationNumber().length() != 0){ 
    			retString += buildSurrounding("ident_nr",searchConditions.getIdentificationNumber(),connector);
    		}  	
    	if ( searchConditions.getManufacturer() != null ) 
    		if(searchConditions.getManufacturer().length() != 0){
    			retString += buildSurrounding("manufacturer",searchConditions.getManufacturer(),connector);
    		}
    	if ( searchConditions.getCarType() != null ) 
    		if(searchConditions.getCarType().length() != 0){
    			retString += buildSurrounding("type",searchConditions.getCarType(),connector);
    		}
    	if ( searchConditions.getModel() != null )
    		if(searchConditions.getModel().length() != 0){ 
    			retString += buildSurrounding("model",searchConditions.getModel(),connector);
    		}
    	if(searchConditions.getColour()!=null){ 
    		retString += buildSurrounding("colour",searchConditions.getColour().getName(),connector);
    	}
    	if(searchConditions.getEngineType()!=null){ 
    		retString += buildSurrounding("engine_type",searchConditions.getEngineType().getName(),connector);
    	}
    	if(searchConditions.getOriginPrice()>0){ 
    		retString += buildSurrounding("purchase_price",searchConditions.getOriginPrice(),connector);
    	}    	
    	if(searchConditions.getPrice()>0){ 
    		retString += buildSurrounding("price",searchConditions.getPrice(),connector);
    	}
    	if(searchConditions.isSelled() == true){
    		retString += buildSurrounding("selled",1,connector);
    	}else{
    		retString += buildSurrounding("selled",0,connector);
    	}
    	if(searchConditions.getYearOfConstruction()>0){ 
    		retString += buildSurrounding("year",searchConditions.getYearOfConstruction(),connector);
    	}
    	if(searchConditions.getMileage()>0){ 
    		retString += buildSurrounding("milage",searchConditions.getMileage(),"");
    	}
    	
    	
   	
    	return retString;
    }    
    
    private Car buildCar(ResultSet rs) throws SQLException{
    	
    	Car aCar = new Car();
    	
    	aCar.setIdentificationNumber(rs.getString("ident_nr"));
    	aCar.setManufacturer(rs.getString("manufacturer"));
    	aCar.setCarType(rs.getString("type"));
    	aCar.setModel(rs.getString("model"));
    	aCar.setColour(Colour.find(rs.getString("colour")));
    	aCar.setEngineType(EngineType.find(rs.getString("engine_type")));
    	aCar.setOriginPrice(rs.getDouble("purchase_price"));
    	aCar.setPrice(rs.getDouble("price"));
    	aCar.setSelled(rs.getBoolean("selled"));
    	aCar.setMileage(rs.getInt("milage"));
    	  	
    	return aCar;
    }    
    
    private String convertObjektToInsertString( Car theCar){
    	
    	String retString = "";
    	
    	retString += "'"+theCar.getIdentificationNumber()+"',";
    	retString += "'"+theCar.getManufacturer()+"',";
    	retString += "'"+theCar.getCarType()+"',";
    	retString += "'"+theCar.getModel()+"',";
    	if ( theCar.getColour() != null ) 
    		retString += "'"+theCar.getColour().getName()+"',";
    	else
    		retString += "'"+Colour.GREEN.getName()+"',";
    	if ( theCar.getEngineType() != null ) 
    		retString += "'"+theCar.getEngineType().getName()+"',";
    	else
    		retString += "'"+EngineType.DIESEL.getName()+"',";
    	retString += theCar.getOriginPrice()+",";
    	retString += theCar.getPrice()+",";
    	retString += "'"+theCar.isSelled()+"',";
    	retString += theCar.getYearOfConstruction()+",";
    	retString += theCar.getMileage();
  	    	
    	return retString;
    }
    
    private String buildSurrounding(String field, String value, String connector){
		
    	String retString = "";
    	retString = connector + " "+ field+" = '"+value+"'";
    	
    	return retString; 
    }    
    private String buildSurrounding(String field, Integer value, String connector){
		
    	String retString = "";
    	retString = connector + " "+ field+" = "+value;
    	
    	return retString; 
    }
    private String buildSurrounding(String field, Double value, String connector){
		
    	String retString = "";
    	retString = connector + " "+ field+" = "+value;
    	
    	return retString; 
    }
//    private String buildSurrounding(String field, Boolean value, String connector){
//		
//    	String retString = "";
//    	retString = field+" = "+value+connector;
//    	
//    	return retString; 
//    }  
}

